﻿using System;
using System.Collections.Generic;
using System.Linq;
using DMS.BaseClass;
using DMS.Business.Modules.Configuation;
using DMS.Business.Modules.SupplierManagement;
using DMS.Business.Modules.Organization;
using System.Web;
using DMS.Base;
using System.Web.Security;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using DMS.Business.BaseClass;

namespace DMS.Business.Utility
{
    public class UserHelper : BaseService
    {
        #region 用户登录/退出相关方法
        /// <summary>
        /// 用户登录
        /// </summary>
        /// <param name="user">用户</param>
        /// <param name="type">登录类型</param>
        /// <param name="message">返回登录信息</param>
        /// <returns></returns>
        public static bool UserLogin(AuthenticationUser user, LoginTypes type, out string message)
        {
            // 用户存在性检查
            if (user == null)
            {
                throw new ArgumentNullException("user", "用户登录时，传入的用户对象不能为空。");
            }

            // 用户有效性检查
            if (user.Status != ActorStatus.Actived)
            {
                message = "无效用户或用户已过期";
                return false;
            }

            // 用户登录
            try
            {
                if (!user.IsInRole("档案管理员;分局管理员;系统管理员;县局管理员;一般管理员"))
                {
                    message = "无权使用系统，请联系管理员添加权限。";
                    return false;
                }

                message = LogHelper.LogUserLogin(user);
                FormsAuthentication.RedirectFromLoginPage(user.LoginID, false);
                return true;
            }
            catch (Exception ex)
            {
                string code = LogHelper.LogException(ex);
                message = string.Format("登录出现异常，请与管理员联系，查询码：{0}", code);
            }

            return false;
        }
        /// <summary>
        /// 用户退出系统
        /// </summary>
        /// <param name="context">系统上下文</param>
        public static void UserLogout(HttpContext context)
        {
            LogHelper.LogUserLogout(SystemContext.Current.LoginAuthUser);
            FormsAuthentication.SignOut();
            context.Response.Redirect(FormsAuthentication.LoginUrl, false);
        }
        #endregion

        #region 部门管理相关方法

        #region SQL查询语句
        /// <summary>
        /// 部门表查询主句
        /// </summary>
        private const string SQL_ORG_Dept = @"SELECT {0} [DeptID]
                                                      ,[P_DeptID]
                                                      ,[DeptName]
                                                      ,[DeptFullName]
                                                      ,[DeptIDPath]
                                                      ,[DeptLevel]
                                                      ,[IsTempDept]
                                                      ,[OrderNo]
                                                  FROM [dbo].[ORG_Deptments]";
        /// <summary>
        /// 部门表统计主句
        /// </summary>
        private const string SQL_ORG_Dept_Count = @"SELECT COUNT(*) AS Total
                                                    FROM [dbo].[ORG_Deptments]";
        /// <summary>
        /// 部门表排序子句
        /// </summary>
        private const string SQL_ORG_Dept_Order = @"ORDER BY [OrderNo]";

        private const string SQL_ORG_DEPT_SCOPE = @"  WITH temp AS(
                                                                                SELECT *
                                                                                FROM   ORG_Deptments
                                                                                WHERE DeptID IN ({0})
                                                                                UNION ALL
                                                                                SELECT a.*
                                                                                FROM   ORG_Deptments a,
                                                                                       temp
                                                                                WHERE  a.P_DeptID = temp.deptID
                                                             ), temp1 AS(
                                                                                SELECT *
                                                                                FROM   ORG_Deptments
                                                                                WHERE DeptID in ({0})
                                                                                UNION ALL
                                                                                SELECT a.*
                                                                                FROM   ORG_Deptments a,
                                                                                       temp1
                                                                                WHERE  a.deptID = temp1.P_DeptID
                                                             )
                                                            SELECT DISTINCT * FROM temp 
                                                            UNION 
                                                            SELECT DISTINCT * FROM temp1 WHERE temp1.deptlevel >1
                                                            ORDER BY deptlevel,OrderNo ";

        private const string SQL_ORG_DEPT_SCOPE_Simple = @"SELECT *
                                                                FROM   ORG_Deptments
                                                                WHERE DeptID IN ({0})
                                                            ORDER BY OrderNo ";
        #endregion

        /// <summary>
        /// 获取部门
        /// </summary>
        /// <param name="id">部门唯一编码</param>
        /// <returns></returns>
        public static Department GetDepartmentByID(string id)
        {
            if (id == "")
            {
                throw new ArgumentNullException("id", "查询部门时，部门唯一编码(ID)不能为空。");
            }

            string condition = string.Empty;
            List<DbParameter> DbParams = new List<DbParameter>();

            condition += " AND [DeptID] = @DeptID";
            DbParameter pDeptID = DBHelper.CreateParamer();
            pDeptID.ParameterName = "DeptID";
            pDeptID.Value = id.ToString().ToUpper();
            DbParams.Add(pDeptID);

            int total = 0;
            List<Department> depts = GetDepartments(-1, 1, out total, condition, DbParams);
            Department dept = depts.DefaultIfEmpty(null).FirstOrDefault();

            return dept;
        }

        /// <summary>
        /// 根据部门ID获取部门名称。多个ID以“;”隔开
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static string GetDeptNameByID(string id)
        {
            string res = "";

            string[] idArray = id.Split(';');
            string condition = "";
            foreach (string str in idArray)
            {
                condition += string.Format("'{0}',",str);
            }
            condition = condition.TrimEnd(',');

            string sql = string.Format("select DeptName from dbo.ORG_Deptments where DeptID in ({0})", condition);
            DataSet ds = DBHelper.Instance.ExecuteDataSet(CommandType.Text,sql);

            if (ds != null)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    res += ds.Tables[0].Rows[i][0].ToString();
                    if (i < ds.Tables[0].Rows.Count - 1)
                        res += ";";
                }
            }
            return res;
        }

        public static Department GetDepartmentByFullName(string fullName)
        {
            if (String.IsNullOrEmpty(fullName))
            {
                throw new ArgumentNullException("fullName", "按部门全名称查询部门时，部门全名称不能为空。");
            }

            string condition = string.Empty;
            List<DbParameter> DbParams = new List<DbParameter>();

            condition += " AND [DeptFullName] = @fullName";
            DbParameter param = DBHelper.CreateParamer();
            param.ParameterName = "fullName";
            param.Value = fullName.ToUpper();
            DbParams.Add(param);

            int total = 0;
            List<Department> depts = GetDepartments(-1, 1, out total, condition, DbParams);
            Department dept = depts.DefaultIfEmpty(null).FirstOrDefault();

            return dept;
        }

        public static Department GetDepartmentByName(string deptName)
        {
            if (String.IsNullOrEmpty(deptName))
            {
                throw new ArgumentNullException("deptName", "按名称查询部门时，部门名称不能为空。");
            }

            string condition = string.Empty;
            List<DbParameter> DbParams = new List<DbParameter>();

            condition += " AND [DeptName] = @DeptName";
            DbParameter pDeptID = DBHelper.CreateParamer();
            pDeptID.ParameterName = "DeptName";
            pDeptID.Value = deptName.ToUpper();
            DbParams.Add(pDeptID);

            int total = 0;
            List<Department> depts = GetDepartments(-1, 1, out total, condition, DbParams);
            Department dept = depts.DefaultIfEmpty(null).FirstOrDefault();

            return dept;
        }

        /// <summary>
        /// 获取部门列表
        /// </summary>
        /// <param name="limite">返回数量限制</param>
        /// <param name="page">页码</param>
        /// <param name="total">总数</param>
        /// <param name="condition">查询字符串</param>
        /// <param name="dbParams">条件参数</param>
        /// <returns></returns>
        public static List<Department> GetDepartments(int limite, int page, out int total, string condition, List<DbParameter> dbParams)
        {
            total = 0;
            Database db = DBHelper.Instance;
            string topClause = string.Empty;
            string whereClause = string.Format(" WHERE 1 = 1 {0}", condition);
            string countSql = string.Format("{0} {1}", SQL_ORG_Dept_Count, whereClause);
            if (limite > 0)
            {
                topClause = string.Format("Top {0}", limite.ToString());

                if (page > 1)
                {
                    int pageCount = (page - 1) * limite;
                    string subClause = string.Format(@"SELECT TOP {0} [DeptID]
                                                       FROM [dbo].[ORG_Deptments] {1} {2}", pageCount.ToString(), whereClause, SQL_ORG_Dept_Order);
                    whereClause += string.Format(" AND [DeptID] NOT IN({0})", subClause);
                }
            }
            string mainClause = string.Format(SQL_ORG_Dept, topClause);
            string sql = string.Format("{0} {1} {2}", mainClause, whereClause, SQL_ORG_Dept_Order);
            DbCommand command = db.GetSqlStringCommand(sql);
            if (dbParams != null)
            {
                command.Parameters.AddRange(dbParams.ToArray());
            }

            DataSet ds = db.ExecuteDataSet(command);
            List<Department> depts = new List<Department>();
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                total = GetRecordCount(countSql, dbParams);
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    Department dept = new Department(dr["DeptID"].ToString());
                    FillDeptInfo(dr, dept);

                    depts.Add(dept);
                }
            }

            return depts;
        }

        /// <summary>
        /// 获取部门列表
        /// </summary>
        /// <param name="limite">返回数量限制</param>
        /// <param name="page">页码</param>
        /// <param name="total">总数</param>
        /// <param name="condition">查询字符串</param>
        /// <param name="dbParams">条件参数</param>
        /// <returns></returns>
        public static List<Department> GetScopeDepartments(string scpoeDeptIDs)
        {
            Database db = DBHelper.Instance;

            string sql = string.Format(SQL_ORG_DEPT_SCOPE, scpoeDeptIDs);
            
            DbCommand command = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(command);
            List<Department> depts = new List<Department>();
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    Department dept = new Department(dr["DeptID"].ToString());
                    FillDeptInfo(dr, dept);

                    depts.Add(dept);
                }
            }

            return depts;
        }

        /// <summary>
        /// 获取部门列表
        /// </summary>
        /// <param name="limite">返回数量限制</param>
        /// <param name="page">页码</param>
        /// <param name="total">总数</param>
        /// <param name="condition">查询字符串</param>
        /// <param name="dbParams">条件参数</param>
        /// <returns></returns>
        public static List<Department> GetScopeDepartmentsSimple(string scpoeDeptIDs)
        {
            Database db = DBHelper.Instance;

            string sql = string.Format(SQL_ORG_DEPT_SCOPE_Simple, scpoeDeptIDs);

            DbCommand command = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(command);
            List<Department> depts = new List<Department>();
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    Department dept = new Department(dr["DeptID"].ToString());
                    FillDeptInfo(dr, dept);

                    depts.Add(dept);
                }
            }

            return depts;
        }

        /// <summary>
        /// 获取用户列表
        /// </summary>
        /// <param name="limite">返回数量限制</param>
        /// <param name="page">页码</param>
        /// <param name="total">总数</param>
        /// <param name="condition">查询字符串</param>
        /// <param name="dbParams">条件参数</param>
        /// <returns></returns>
        public static List<User> GetScopeUsers(int limite, int page, out int total, string condition, List<DbParameter> dbParams, string scopeIDs, string ContextDeptID)
        {
            total = 0;
            Database db = DBHelper.Instance;
            string topClause = string.Empty;
            string whereClause = string.Format("WHERE 1 = 1 {0}", condition);
            string countSql = string.Format("{0} {1}", string.Format(SQL_SCOPE_ORG_User_Count, string.IsNullOrEmpty(ContextDeptID) ? scopeIDs : "'" + ContextDeptID + "'"), whereClause);
            if (limite > 0)
            {
                topClause = string.Format("Top {0}", limite.ToString());

                if (page > 1)
                {
                    int pageCount = (page - 1) * limite;
                    string subClause = string.Format(@"SELECT TOP {0} ou.UserID FROM temp
                                                        JOIN ORG_Users ou ON temp.deptid=ou.DeptID  {1} {2}", pageCount.ToString(), whereClause, SQL_SCOPE_ORG_User_OrderBy);
                    whereClause += string.Format("AND [UserID] NOT IN({0})", subClause);
                }
            }
            string mainClause = string.Format(SQL_SCOPE_ORG_User, string.IsNullOrEmpty(ContextDeptID) ? scopeIDs : "'"+ContextDeptID+"'", topClause);
            string sql = string.Format("{0} {1} {2}", mainClause, whereClause, SQL_SCOPE_ORG_User_OrderBy);
            DbCommand command = db.GetSqlStringCommand(sql);
            if (dbParams != null && dbParams.Count > 0)
            {
                command.Parameters.AddRange(dbParams.ToArray());
            }

            DataSet ds = db.ExecuteDataSet(command);
            List<User> users = new List<User>();
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                total = GetRecordCount(countSql, dbParams);
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    User user = new User(dr["UserID"].ToString());
                    FillUserInfo(dr, user);

                    users.Add(user);
                }
            }

            return users;
        }

        /// <summary>
        /// 返回根部门列表
        /// </summary>
        /// <returns></returns>
        public static List<Department> GetRootDepartments()
        {
            int total = 0;
            List<Department> depts = GetDepartments(-1, 1, out total, AppParams.RootDeptCondition, null);

            return depts;
        }
        /// <summary>
        /// 获取子部门列表
        /// </summary>
        /// <param name="pDept">父部门</param>
        /// <returns></returns>
        public static List<Department> GetSubDepartments(Department pDept)
        {
            if (pDept == null)
            {
                throw new ArgumentNullException("pDept", "获取子部门列表时，转入的父部门对象不能为空");
            }


            string condition = string.Empty;
            List<DbParameter> DbParams = new List<DbParameter>();

            condition += " AND [P_DeptID] = @P_DeptID";
            DbParameter pDeptID = DBHelper.CreateParamer();
            pDeptID.ParameterName = "P_DeptID";
            pDeptID.Value = pDept.ID.ToString().ToUpper();
            DbParams.Add(pDeptID);

            int total = 0;
            List<Department> depts = GetDepartments(-1, 1, out total, condition, DbParams);

            return depts;
        }

        /// <summary>
        /// 是否拥有子部门
        /// </summary>
        /// <param name="deptID"></param>
        /// <returns></returns>
        public static bool HasSubDeptments(string deptID)
        {
            string sql = string.Format("select count(0) from ORG_Deptments where P_DeptID='{0}'",deptID);
            object res = DBHelper.Instance.ExecuteScalar(CommandType.Text, sql);
            return int.Parse(res.ToString()) > 0;
        }

        /// <summary>
        /// 填充部门信息数据
        /// </summary>
        /// <param name="dr">部门信息数据行</param>
        /// <param name="dept">部门对象</param>
        private static void FillDeptInfo(DataRow dr, Department dept)
        {
            // 检查数据行参数
            if (dr == null)
            {
                throw new ArgumentNullException("dr", "填充部门信息时，部门数据行dr不能为空。");
            }
            if (dr["DeptID"] == null || dr["DeptID"].ToString().Trim() == string.Empty)
            {
                throw new ArgumentNullException("DeptID", "填充部门信息时，部门数据行中的DeptID字段为空值，请检查数据。");
            }

            // 检查部门参数
            if (dept == null)
            {
                string deptID = dr["DeptID"].ToString();
                string pDeptID = "";

                if (dr["P_DeptID"] != null && dr["P_DeptID"].ToString().Trim() != string.Empty)
                {
                    pDeptID = dr["P_DeptID"].ToString();
                }

                dept = new Department(deptID, pDeptID);
            }

            // 装载部门信息
            if (dr["P_DeptID"] != null && dr["P_DeptID"].ToString().Trim() != string.Empty)
            {
                dept.ParentID = dr["P_DeptID"].ToString();
            }
            dept.Name = dr["DeptName"].ToString();
            dept.FullName = dr["DeptFullName"].ToString();
            dept.IDPath = dr["DeptIDPath"].ToString();
            if (dr["DeptLevel"] != null && dr["DeptLevel"].ToString() != string.Empty)
            {
                dept.Level = Convert.ToInt32(dr["DeptLevel"].ToString());
            }
            if (dr["OrderNo"] != null && dr["OrderNo"].ToString() != string.Empty)
            {
                dept.Sequence = Convert.ToInt32(dr["OrderNo"].ToString());
            }
            dept.IsTemporary = dr["IsTempDept"].ToString() == "1";
        }

        #endregion

        #region 用户管理相关方法

        #region SQL查询句子
        /// <summary>
        /// 用户表查询主句
        /// </summary>
        private const string SQL_ORG_User = @"SELECT {0} [ORG_Users].[UserID]
                                                      ,[LoginID]
                                                      ,[DeptID]
                                                      ,[BigDeptID]
                                                      ,[EmployeeID]
                                                      ,[UserName]
                                                      ,[Pinyin]
                                                      ,[CardCode]
                                                      ,[Sex]
                                                      ,[Birthday]
                                                      ,[UserLigion]
                                                      ,[UserNation]
                                                      ,[UserNative]
                                                      ,[OrderNo]
                                                      ,[Mobile]
                                                      ,[Telephone]
                                                      ,[Email]
                                                      ,[JobTypeID]
                                                      ,[UserType]
                                                      ,[Labour]
                                                      ,[UserGrade]
                                                      ,[UserLevel]
                                                      ,[UserCurrentLevel]
                                                      ,[UserPosition]
                                                      ,[UserJoinInDate]
                                                      ,[UserStatus]
                                                      ,[isTmpUser]
                                                      ,[StaticPWD]
                                                  FROM [dbo].[ORG_Users]";
        /// <summary>
        /// 代理用户查询主句
        /// </summary>
        private const string SQL_ORG_Delegate_User = @"SELECT [ORG_Users].[UserID]
                                                      ,[LoginID]
                                                      ,[DeptID]
                                                      ,[UserName]
                                                  FROM [dbo].[ORG_Users]";

        /// <summary>
        /// 代理审批的源用户查询主句
        /// </summary>
        private const string SQL_ORG_Original_User = @"SELECT ORIGINALPARTID
		                                                ,ORIGINALPARTNAME
		                                                ,ORIGINALPARTDEPTID
		                                                ,ORIGINALPARTDEPTNAME 
                                                FROM WF_WORKITEMS
                                                WHERE WID='{0}'";
        /// <summary>
        /// 用户表统计主句
        /// </summary>
        private const string SQL_ORG_User_Count = @"SELECT COUNT(*) AS Total
                                                    FROM [dbo].[ORG_Users]";
        /// <summary>
        /// 用户表排序子句
        /// </summary>
        private const string SQL_ORG_User_OrderBy = "ORDER BY OrderNo";

        private const string SQL_SCOPE_ORG_User_Count = @"WITH temp AS(
                                                                            SELECT *
                                                                            FROM   ORG_Deptments
                                                                            WHERE DeptID IN ({0})
                                                                            UNION ALL
                                                                            SELECT a.*
                                                                            FROM   ORG_Deptments a,
                                                                                   temp
                                                                            WHERE  a.P_DeptID = temp.deptID
                                                         )
                                                        SELECT COUNT(*) AS Total FROM temp 
                                                        JOIN ORG_Users ou ON temp.deptid=ou.DeptID";

        private const string SQL_SCOPE_ORG_User = @"WITH temp AS(
                                                                            SELECT *
                                                                            FROM   ORG_Deptments
                                                                            WHERE DeptID IN ({0})
                                                                            UNION ALL
                                                                            SELECT a.*
                                                                            FROM   ORG_Deptments a,
                                                                                   temp
                                                                            WHERE  a.P_DeptID = temp.deptID
                                                         )
                                                        SELECT {1} ou.* FROM temp
                                                        JOIN ORG_Users ou ON temp.deptid=ou.DeptID";
        
        private const string SQL_SCOPE_ORG_User_OrderBy = "ORDER BY ou.OrderNo";

        #endregion

        /// <summary>
        /// 根据用户唯一编码（UserID）获取用户
        /// </summary>
        /// <param name="userID">用户唯一编码（UserID）</param>
        /// <returns></returns>
        public static User GetUserByID(string userID)
        {
            if(string.IsNullOrEmpty(userID))
            {
                return null;
            }
            int total = 0;
            string condition = string.Empty;
            List<DbParameter> dbParams = new List<DbParameter>();

            condition += " AND [UserID] = @UserID";
            DbParameter p_UserID = DBHelper.CreateParamer();
            p_UserID.ParameterName = "UserID";
            p_UserID.Value = userID.ToString().ToUpper();
            dbParams.Add(p_UserID);

            User user = UserHelper.GetUsers(-1, 1, out total, condition, dbParams).DefaultIfEmpty(null).FirstOrDefault();
            return user;
        }
        /// <summary>
        /// 根据用户登录帐号（LoginID）获取用户
        /// </summary>
        /// <param name="loginID">用户登录帐号（LoginID）</param>
        /// <returns></returns>
        public static User GetUserByLoginID(string loginID)
        {
            if(string.IsNullOrEmpty(loginID))
            {
                return null;
            }
            int total = 0;
            string condition = string.Empty;
            List<DbParameter> dbParams = new List<DbParameter>();

            condition += " AND [LoginID] = @LoginID";
            DbParameter p_LoginID = DBHelper.CreateParamer();
            p_LoginID.ParameterName = "LoginID";
            p_LoginID.Value = loginID;
            dbParams.Add(p_LoginID);

            User user = UserHelper.GetUsers(-1, 1, out total, condition, dbParams).DefaultIfEmpty(null).FirstOrDefault();
            return user;
        }
        /// <summary>
        /// 获取用户列表
        /// </summary>
        /// <param name="limite">返回数量限制</param>
        /// <param name="page">页码</param>
        /// <param name="total">总数</param>
        /// <param name="condition">查询字符串</param>
        /// <param name="dbParams">条件参数</param>
        /// <returns></returns>
        public static List<User> GetUsers(int limite, int page, out int total, string condition, List<DbParameter> dbParams)
        {
            total = 0;
            Database db = DBHelper.Instance;
            string topClause = string.Empty;
            string whereClause = string.Format("WHERE 1 = 1 {0}", condition);
            string countSql = string.Format("{0} {1}", SQL_ORG_User_Count, whereClause);
            if (limite > 0)
            {
                topClause = string.Format("Top {0}", limite.ToString());

                if (page > 1)
                {
                    int pageCount = (page - 1) * limite;
                    string subClause = string.Format(@"SELECT TOP {0} [UserID]
                                                       FROM [dbo].[ORG_Users] {1} {2}", pageCount.ToString(), whereClause, SQL_ORG_User_OrderBy);
                    whereClause += string.Format("AND [UserID] NOT IN({0})", subClause);
                }
            }
            string mainClause = string.Format(SQL_ORG_User, topClause);
            string sql = string.Format("{0} {1} {2}", mainClause, whereClause, SQL_ORG_User_OrderBy);
            DbCommand command = db.GetSqlStringCommand(sql);
            if (dbParams != null && dbParams.Count > 0)
            {
                command.Parameters.AddRange(dbParams.ToArray());
            }

            DataSet ds = db.ExecuteDataSet(command);
            List<User> users = new List<User>();
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                total = GetRecordCount(countSql, dbParams);
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    User user = new User(dr["UserID"].ToString());
                    FillUserInfo(dr, user);

                    users.Add(user);
                }
            }

            return users;
        }

        /// <summary>
        /// 获取当前登录用户
        /// </summary>
        /// <returns></returns>
        public static User GetContextUser()
        {
            User contextUser = null;

            HttpContext context = HttpContext.Current;
            if (context !=null && context.User.Identity.IsAuthenticated)
            {
                string userLoginID = context.User.Identity.Name;
                //if (HttpContext.Current.Cache["LoginUser" + userLoginID] == null)
                //{
                //    contextUser = UserHelper.GetUserByLoginID(userLoginID);
                //    if (contextUser != null)
                //        HttpContext.Current.Cache["LoginUser" + userLoginID] = contextUser;
                //}
                //else
                //{
                //    contextUser = HttpContext.Current.Cache["LoginUser" + userLoginID] as User;
                //}
                contextUser = UserHelper.GetUserByLoginID(userLoginID);
            }

            return contextUser;
        }
        ///// <summary>
        ///// 是否室经理
        ///// </summary>
        ///// <param name="userID"></param>
        ///// <returns></returns>
        //public static bool IsManager(Guid userID)
        //{
        //    int total = 0;
        //    string condition = AppParams.DeptManagerCondition;
        //    List<DbParameter> dbParams = new List<DbParameter>();

        //    condition += " AND [UserID] = @UserID";
        //    DbParameter p_UserID = DBHelper.CreateParamer();
        //    p_UserID.ParameterName = "UserID";
        //    p_UserID.Value = userID.ToString().ToUpper();
        //    dbParams.Add(p_UserID);

        //    List<User> users = UserHelper.GetUsers(1, 0, out total, condition, dbParams);
        //    users = null;

        //    return total > 0;
        //}

        /// <summary>
        /// 获取部门用户
        /// </summary>
        /// <param name="dept">目标部门</param>
        /// <returns></returns>
        public static List<User> GetDeptUsers(Department dept)
        {
            if (dept == null)
            {
                throw new ArgumentNullException("dept", "获取部门用户时，传入的部门对象不能为空");
            }

            int total = 0;
            string condition = string.Empty;
            List<DbParameter> dbParams = new List<DbParameter>();

            condition += " AND [DeptID] = @DeptID";
            DbParameter p_DeptID = DBHelper.CreateParamer();
            p_DeptID.ParameterName = "DeptID";
            p_DeptID.Value = dept.ID.ToString().ToUpper();
            dbParams.Add(p_DeptID);

            List<User> users = UserHelper.GetUsers(-1, 1, out total, condition, dbParams);
            return users;
        }

        /// <summary>
        /// 填充用户信息
        /// </summary>
        /// <param name="dr">用户数据行</param>
        /// <param name="user">用户</param>
        private static void FillUserInfo(DataRow dr, User user)
        {
            // 检查数据行参数
            if (dr == null)
            {
                throw new ArgumentNullException("dr", "填充用户信息时，用户数据行dr不能为空。");
            }
            if (dr["UserID"] == null || dr["UserID"].ToString().Trim() == string.Empty)
            {
                throw new ArgumentNullException("UserID", "填充用户信息时，用户数据行中的UserID字段为空值，请检查数据。");
            }

            // 检查用户参数
            if (user == null)
            {
                user = new User(dr["UserID"].ToString());
            }

            // 装载用户信息
            try
            {
                //通过用户ID获取代理用户 
                AuthenticationUser delegateUser = getDelegateUserByUserID(user.ID.ToString());
                user.DelegateUser = delegateUser;

                user.LoginID = dr["LoginID"].ToString();

                user._DeptID = dr["DeptID"].ToString();
                if (dr["BigDeptID"] != null && dr["BigDeptID"].ToString() != string.Empty)
                    user._BigDeptID = dr["BigDeptID"].ToString();

                user.Name = dr["UserName"].ToString();
                user.Pinyin = dr["Pinyin"].ToString();
                user.CarCode = dr["CardCode"].ToString();
                if (dr["Sex"] != null && dr["Sex"].ToString() != string.Empty)
                {
                    user.Sex = (SexTypes)Enum.Parse(typeof(SexTypes), dr["Sex"].ToString());
                }
                if (dr["Birthday"] != null && dr["Birthday"].ToString() != string.Empty)
                {
                    user.Birthday = Convert.ToDateTime(dr["Birthday"].ToString());
                }
                if (dr["UserLigion"] != null && dr["UserLigion"].ToString() != string.Empty)
                {
                    user.Ligion = (LigionTypes)Enum.Parse(typeof(LigionTypes), dr["UserLigion"].ToString());
                }
                user.Nation = dr["UserNation"].ToString();
                user.Native = dr["UserNative"].ToString();

                user.Mobile = dr["Mobile"].ToString();
                user.Telephone = dr["Telephone"].ToString();
                user.Email = dr["Email"].ToString();

                user.EmployeeID = dr["EmployeeID"].ToString();
                //[UserType]
                if (dr["JobTypeID"] != null && dr["JobTypeID"].ToString() != string.Empty)
                {
                    user.JobType = (JobTypes)Enum.Parse(typeof(JobTypes), dr["JobTypeID"].ToString());
                }
                user.Labour = dr["Labour"].ToString();
                user.UserGrade = dr["UserGrade"].ToString();
                user.PositionName = dr["UserPosition"].ToString();
                user.PosLevel = dr["UserLevel"].ToString();
                user.PosCurrentLevel = dr["UserCurrentLevel"].ToString();
                if (dr["UserJoinInDate"] != null && dr["UserJoinInDate"].ToString() != string.Empty)
                {
                    user.JoinInDate = Convert.ToDateTime(dr["UserJoinInDate"].ToString());
                }

                if (dr["OrderNo"] != null && dr["OrderNo"].ToString() != string.Empty)
                {
                    user.Sequence = Convert.ToInt32(dr["OrderNo"].ToString());
                }
                if (dr["UserStatus"] != null && dr["UserStatus"].ToString() != string.Empty)
                {
                    user.Status = (ActorStatus)Enum.Parse(typeof(ActorStatus), dr["UserStatus"].ToString());
                }
                user.IsTemporary = dr["isTmpUser"].ToString() == "1";
                user.PasswordCode = string.IsNullOrEmpty(dr["StaticPWD"].ToString())
                                        ? "E10ADC3949BA59ABBE56E057F20F883E"
                                        : dr["StaticPWD"].ToString();
            }
            catch (Exception ex)
            {
                string msg = string.Format("用户\"{0}\"({1})在装载属性信息时出错", user.Name, user.LoginID);
                LogHelper.LogException(ex, msg);

                user = null;
            }
        }

        //通过用户ID获取代理用户
        public static AuthenticationUser getDelegateUserByUserID(string userID)
        {
            User delegateUser = null;
            string whereClause = @" where userid='{0}'";
            string mainClause = SQL_ORG_Delegate_User + whereClause;

            string delegateUserID = null;
            string configId = "DelegateUser";
            string memberID = userID;

            //读取代理用户属性，若不存在，说明未设置，直接返回null，否则读取代理用户ID，并查找相关信息
            List<MemberConfig> memberConfigs = ConfigHelper.GetMemberConfigs(memberID);
            MemberConfig config = (from c in memberConfigs
                                   where c.ID == configId
                                   select c).ToList().DefaultIfEmpty(null).FirstOrDefault();
            if (config == null)
                return null;
            else {
                delegateUserID = config.Value;
            }

            if (!string.IsNullOrEmpty(delegateUserID)) {
                string sql = String.Format(mainClause, delegateUserID);
                Database db = DBHelper.Instance;
                DbCommand command = db.GetSqlStringCommand(sql);
                DataSet ds = db.ExecuteDataSet(command);

                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    DataRow dr = ds.Tables[0].Rows[0];
                    delegateUser = new User(dr["UserID"].ToString());
                    delegateUser.Name = dr["UserName"].ToString();
                    delegateUser._DeptID = dr["DeptID"].ToString();
                    delegateUser.Dept = GetDepartmentByID(delegateUser._DeptID);
                }
            }
            return delegateUser;
        }

        //根据workitemId获取原申请人
        public static DataRow getOriginalUserInfo(string workItemID)
        {
            if (!string.IsNullOrEmpty(workItemID))
            {
                string sql = string.Format(SQL_ORG_Original_User, workItemID);
                Database db = DBHelper.Instance;
                DbCommand command = db.GetSqlStringCommand(sql);
                DataSet ds = db.ExecuteDataSet(command);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    if (!ds.Tables[0].Rows[0].IsNull("ORIGINALPARTID")
                        &&!string.IsNullOrEmpty(ds.Tables[0].Rows[0]["ORIGINALPARTID"].ToString()))
                        return ds.Tables[0].Rows[0];
                    else
                        return null;
                }
                else
                    return null;
            }
            else
                return null;
            
        }

        /// <summary>
        /// 判断用户是否在代理期间。为兼容旧版本，当代理时间为空时，默认返回true
        /// </summary>
        /// <param name="participant">参与者</param>
        /// <returns></returns>
        public static bool IsDuringDelegate(AuthenticationUser participant)
        {
            bool isDuringDelegate = true;
            //兼容以前的版本，当代理时间没设置时，默认当做还在代理期间
            if (!string.IsNullOrEmpty(participant.DelegateBeginTime) && !string.IsNullOrEmpty(participant.DelegateEndTime))
            {
                if (DateTime.Now >= DateTime.Parse(participant.DelegateBeginTime + " 00:00:00")
                    && DateTime.Now <= DateTime.Parse(participant.DelegateEndTime + " 23:59:59"))
                    isDuringDelegate = true;
                else
                    isDuringDelegate = false;
            }

            return isDuringDelegate;

        }

        #endregion

        #region 角色管理相关方法

        #region SQL查询句子
        /// <summary>
        /// 角色表查询主句
        /// </summary>
        private const string SQL_ORG_Role = @"SELECT {0} [RoleID]
                                                      ,[RoleName]
                                                      ,[RoleType]
                                                      ,[IsBuildIn]
                                                      ,[IsDisplay]
                                                  FROM [dbo].[ORG_Roles]";
        /// <summary>
        /// 角色表统计主句
        /// </summary>
        private const string SQL_ORG_Role_Count = @"SELECT COUNT(*) AS Total
                                                    FROM [dbo].[ORG_Roles]";
        /// <summary>
        /// 角色排序子句
        /// </summary>
        private const string SQL_ORG_Role_Order = @"ORDER BY [RoleName]";

        private const string SQL_ORG_Role_Add = @"INSERT INTO [dbo].[ORG_Roles]
                                                               ([RoleID]
                                                               ,[RoleName]
                                                               ,[RoleType]
                                                               ,[IsBuildIn]
                                                               ,[IsDisplay])
                                                         VALUES
                                                               (@RoleID
                                                               ,@RoleName
                                                               ,@RoleType
                                                               ,0
                                                               ,1)";
        private const string SQL_ORG_Role_Update = @"UPDATE [dbo].[ORG_Roles]
                                                       SET [RoleName] = @RoleName
                                                          ,[RoleType] = @RoleType
                                                     WHERE [RoleID] = @RoleID";
        private const string SQL_ORG_Role_Delete = @"DELETE FROM [dbo].[ORG_Roles]
                                                     WHERE [RoleID] = @RoleID";
        #endregion

        /// <summary>
        /// 获取角色
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static Role GetRoleByID(string id)
        {
            int total = 0;
            string condition = string.Empty;
            List<DbParameter> dbParams = new List<DbParameter>();

            condition += " AND [RoleID] = @RoleID";
            DbParameter p_RoleID = DBHelper.CreateParamer();
            p_RoleID.ParameterName = "RoleID";
            p_RoleID.Value = id.ToString().ToUpper();
            dbParams.Add(p_RoleID);

            Role role = UserHelper.GetRoles(-1, 1, out total, condition, dbParams).DefaultIfEmpty(null).FirstOrDefault();
            return role;
        }
        /// <summary>
        /// 获取角色
        /// </summary>
        /// <param name="roleName"></param>
        /// <returns></returns>
        public static Role GetRoleByName(string roleName)
        {
            int total = 0;
            string condition = string.Empty;
            List<DbParameter> dbParams = new List<DbParameter>();

            condition += " AND [RoleName] = @RoleName";
            DbParameter p_RoleName = DBHelper.CreateParamer();
            p_RoleName.ParameterName = "RoleName";
            p_RoleName.Value = roleName;
            dbParams.Add(p_RoleName);

            Role role = UserHelper.GetRoles(-1, 1, out total, condition, dbParams).DefaultIfEmpty(null).FirstOrDefault();
            return role;
        }
        /// <summary>
        /// 获取角色列表
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public static List<Role> GetRolesByType(RoleTypes type)
        {
            int total = 0;
            string condition = string.Empty;
            List<DbParameter> dbParams = new List<DbParameter>();

            condition += " AND [RoleType] = @RoleType";
            DbParameter p_RoleType = DBHelper.CreateParamer();
            p_RoleType.ParameterName = "RoleType";
            p_RoleType.Value = (int)type;
            dbParams.Add(p_RoleType);

            List<Role> roles = UserHelper.GetRoles(-1, 1, out total, condition, dbParams);
            return roles;
        }
        /// <summary>
        /// 获取角色列表
        /// </summary>
        /// <param name="limite">返回数量限制</param>
        /// <param name="page">页码</param>
        /// <param name="total">总数</param>
        /// <param name="condition">查询字符串</param>
        /// <param name="dbParams">条件参数</param>
        /// <returns></returns>
        public static List<Role> GetRoles(int limite, int page, out int total, string condition, List<DbParameter> dbParams)
        {
            total = 0;
            Database db = DBHelper.Instance;
            string topClause = string.Empty;
            string whereClause = string.Format("WHERE 1 = 1 and IsDisplay=1 {0}", condition);
            string countSql = string.Format("{0} {1}", SQL_ORG_Role_Count, whereClause);
            if (limite > 0)
            {
                topClause = string.Format("Top {0}", limite.ToString());

                if (page > 1)
                {
                    int pageCount = (page - 1) * limite;
                    string subClause = string.Format(@"SELECT TOP {0} [RoleID]
                                                       FROM [dbo].[ORG_Roles] {1} {2}", pageCount.ToString(), whereClause, SQL_ORG_Role_Order);
                    whereClause += string.Format("AND [RoleID] NOT IN({0})", subClause);
                }
            }
            string mainClause = string.Format(SQL_ORG_Role, topClause);
            string sql = string.Format("{0} {1} {2}", mainClause, whereClause, SQL_ORG_Role_Order);
            DbCommand command = db.GetSqlStringCommand(sql);
            if (dbParams != null && dbParams.Count > 0)
            {
                command.Parameters.AddRange(dbParams.ToArray());
            }

            DataSet ds = db.ExecuteDataSet(command);
            List<Role> roles = new List<Role>();
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                total = GetRecordCount(countSql, dbParams);
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    Role role = new Role(dr["RoleID"].ToString());
                    FillRoleInfo(dr, role);

                    roles.Add(role);
                }
            }

            return roles;
        }

        public static void AddRole(Role role)
        {
            Database db = DBHelper.Instance;
            DbCommand cmd = db.GetSqlStringCommand(SQL_ORG_Role_Add);
            FillRoleParams(cmd, role);

            AuthenticationUser aUser = SystemContext.Current.LoginAuthUser;
            string actionContent = string.Format("用户“{0}({1})”于“{2}”新增了成员角色“{3}”的属性。"
                                                , aUser.Name
                                                , aUser.LoginID
                                                , DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss")
                                                , role.Name);
            LogHelper.LogAction("新增成员角色", actionContent);
            db.ExecuteNonQuery(cmd);
        }
        public static void UpdateRole(Role role)
        {
            if (role == null)
            {
                throw new ArgumentNullException("role", "保存成员角色时，传入的成员角色对象role不能为空。");
            }

            Database db = DBHelper.Instance;
            DbCommand cmd = db.GetSqlStringCommand(SQL_ORG_Role_Update);
            FillRoleParams(cmd, role);

            AuthenticationUser aUser = SystemContext.Current.LoginAuthUser;
            string actionContent = string.Format("用户“{0}({1})”于“{2}”修改了成员角色“{3}”的属性。"
                                                , aUser.Name
                                                , aUser.LoginID
                                                , DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss")
                                                , role.Name);
            LogHelper.LogAction("修改成员角色", actionContent);
            db.ExecuteNonQuery(cmd);
        }
        public static void DeleteRole(Role role)
        {
            if (role == null)
            {
                throw new ArgumentNullException("role", "删除成员角色时，传入的成员角色对象role不能为空。");
            }

            // 是否内置
            if (role.IsBuildIn)
            {
                throw new LogicException(string.Format("角色成员“”为内置角色，不能被删除！", role.Name));
            }

            // 查成员
            int memberCount = role.Members.Count;
            if (memberCount > 0)
            {
                LogicException ex = new LogicException(string.Format("该成员角色下仍有{0}个成员，请先删除本成员角色下的所有成员。", memberCount));
                throw ex;
            }

            Database db = DBHelper.Instance;
            DbCommand cmd = db.GetSqlStringCommand(SQL_ORG_Role_Delete);
            FillRoleParams(cmd, role);

            AuthenticationUser aUser = SystemContext.Current.LoginAuthUser;
            string actionContent = string.Format("用户“{0}({1})”于“{2}”删除了成员角色“{3}”的属性。"
                                                , aUser.Name
                                                , aUser.LoginID
                                                , DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss")
                                                , role.Name);
            LogHelper.LogAction("删除成员角色", actionContent);
            db.ExecuteNonQuery(cmd);
        }

        /// <summary>
        /// 填充角色信息
        /// </summary>
        /// <param name="dr">角色数据行</param>
        /// <param name="role">角色</param>
        private static void FillRoleInfo(DataRow dr, Role role)
        {
            // 检查数据行参数
            if (dr == null)
            {
                throw new ArgumentNullException("dr", "填充角色信息时，角色数据行dr不能为空。");
            }
            if (dr["RoleID"] == null || dr["RoleID"].ToString().Trim() == string.Empty)
            {
                throw new ArgumentNullException("RoleID", "填充角色信息时，用户数据行中的RoleID字段为空值，请检查数据。");
            }

            // 检查用户参数
            if (role == null)
            {
                role = new Role(dr["RoleID"].ToString());
            }

            // 装载角色信息
            role.Name = dr["RoleName"].ToString();
            role.Type = (RoleTypes)Enum.Parse(typeof(RoleTypes), dr["RoleType"].ToString());
            role.IsBuildIn = dr["IsBuildIn"].ToString() == "1";
            role.IsDisplay = dr["IsDisplay"].ToString() == "1";

            role._Data = dr;
        }
        private static void FillRoleParams(DbCommand cmd, Role role)
        {
            DbParameter p_RoleID = cmd.CreateParameter();
            p_RoleID.ParameterName = "RoleID";
            p_RoleID.Value = role.ID.ToString().ToUpper();
            cmd.Parameters.Add(p_RoleID);

            DbParameter p_RoleName = cmd.CreateParameter();
            p_RoleName.ParameterName = "RoleName";
            p_RoleName.Value = role.Name;
            cmd.Parameters.Add(p_RoleName);

            DbParameter p_RoleType = cmd.CreateParameter();
            p_RoleType.ParameterName = "RoleType";
            p_RoleType.Value = (int)role.Type;
            cmd.Parameters.Add(p_RoleType);
        }
        #endregion

        #region 角色成员管理相关方法

        #region SQL查询句子
        /// <summary>
        /// 角色成员查询主句
        /// </summary>
        private const string SQL_ROG_RoleMember = @"SELECT [RoleID]
                                                          ,[MemberID]
                                                          ,[MemberType]
                                                          ,[Scope]
                                                      FROM [dbo].[ORG_RoleMembers]";
        /// <summary>
        /// 角色成员查询主句
        /// </summary>
        private const string SQL_ROG_RoleMember_Condition = @"SELECT [RoleID]
                                                              ,[MemberID]
                                                              ,[MemberType]
                                                              ,[Scope]
                                                          FROM [dbo].[ORG_RoleMembers] orm join org_users ou 
                                                                            on orm.memberId = ou.userid 
                                                            join org_deptments od on ou.deptid=od.deptid ";
        #endregion

        /// <summary>
        /// 查询角色中的成员
        /// </summary>
        /// <param name="role">角色</param>
        /// <returns></returns>
        public static List<Actor> GetRoleMembers(Role role)
        {
            if (role == null)
            {
                throw new ArgumentNullException("role", "查询角色中的成员时，传入的角色对象不能为空。");
            }

            // 取MemberIDs
            Database db = DBHelper.Instance;
            string mainClause = string.Format(SQL_ROG_RoleMember, string.Empty);
            string sql = string.Format("{0} Where [RoleID] = @RoleID", mainClause);
            DbCommand command = db.GetSqlStringCommand(sql);

            DbParameter pRoleID = command.CreateParameter();
            pRoleID.ParameterName = "RoleID";
            pRoleID.Value = role.ID.ToString().ToUpper();
            command.Parameters.Add(pRoleID);

            string memberIDs = string.Empty;
            DataSet ds_mIDs = db.ExecuteDataSet(command);
            if (ds_mIDs.Tables.Count > 0 && ds_mIDs.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr_member in ds_mIDs.Tables[0].Rows)
                {
                    memberIDs += string.Format("'{0}',", dr_member["MemberID"].ToString().ToUpper());
                }

                if (memberIDs.EndsWith(","))
                {
                    memberIDs = memberIDs.TrimEnd(",".ToCharArray());
                }
            }

            // 取内部用户
            int totalUser = 0;
            string conditionStr_User = "AND 1 = 0";
            if (memberIDs != string.Empty)
            {
                conditionStr_User = string.Format("AND [UserID] IN ({0})", memberIDs);
            }
            List<User> users = UserHelper.GetUsers(-1, 1, out totalUser, conditionStr_User, null);
            foreach (User user in users)
            {
                DataTable dt = ds_mIDs.Tables[0];
                DataRow[] drs = dt.Select(string.Format("MemberID = '{0}'", user.ID.ToString()));
                user.MemberScope = drs[0]["Scope"].ToString();
            }

            // 取部门
            int totalDept = 0;
            string conditionStr_Dept = "AND 1 = 0";
            if (memberIDs != string.Empty)
            {
                conditionStr_Dept = string.Format("AND [DeptID] IN ({0})", memberIDs);
            }
            List<Department> depts = UserHelper.GetDepartments(-1, 1, out totalDept, conditionStr_Dept, null);

            List<Actor> members = new List<Actor>();
            foreach (User user in users)
            {
                members.Add(user as Actor);
            }
            foreach (Department dept in depts)
            {
                members.Add(dept as Actor);
            }

            return members;
        }

        /// <summary>
        /// 查询角色中的成员
        /// </summary>
        /// <param name="role">角色</param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public static List<Actor> GetRoleMembersWithCondition(Role role,string condition)
        {
            if (role == null)
            {
                throw new ArgumentNullException("role", "查询角色中的成员时，传入的角色对象不能为空。");
            }

            // 取MemberIDs
            Database db = DBHelper.Instance;
            string mainClause = string.Format(SQL_ROG_RoleMember_Condition, string.Empty);
            string sql = string.Format("{0} Where [RoleID] = '{1}' {2}", mainClause,role.ID.ToUpper(),condition);

            string memberIDs = string.Empty;
            DataSet ds_mIDs = db.ExecuteDataSet(CommandType.Text,sql);//db.ExecuteDataSet(command);
            if (ds_mIDs.Tables.Count > 0 && ds_mIDs.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr_member in ds_mIDs.Tables[0].Rows)
                {
                    memberIDs += string.Format("'{0}',", dr_member["MemberID"].ToString().ToUpper());
                }

                if (memberIDs.EndsWith(","))
                {
                    memberIDs = memberIDs.TrimEnd(",".ToCharArray());
                }
            }

            // 取内部用户
            int totalUser = 0;
            string conditionStr_User = "AND 1 = 0";
            if (memberIDs != string.Empty)
            {
                conditionStr_User = string.Format("AND [UserID] IN ({0})", memberIDs);
            }
            List<User> users = UserHelper.GetUsers(-1, 1, out totalUser, conditionStr_User, null);
            foreach (User user in users)
            {
                DataTable dt = ds_mIDs.Tables[0];
                DataRow[] drs = dt.Select(string.Format("MemberID = '{0}'", user.ID.ToString()));
                user.MemberScope = drs[0]["Scope"].ToString();
            }

            // 取部门
            int totalDept = 0;
            string conditionStr_Dept = "AND 1 = 0";
            if (memberIDs != string.Empty)
            {
                conditionStr_Dept = string.Format("AND [DeptID] IN ({0})", memberIDs);
            }
            List<Department> depts = UserHelper.GetDepartments(-1, 1, out totalDept, conditionStr_Dept, null);

            List<Actor> members = new List<Actor>();
            foreach (User user in users)
            {
                members.Add(user as Actor);
            }
            foreach (Department dept in depts)
            {
                members.Add(dept as Actor);
            }

            return members;
        }
        #endregion


        /// <summary>
        /// 获取角色中的用户
        /// </summary>
        /// <param name="roleName">角色名称</param>
        /// <returns></returns>
        public static List<User> GetUsersByRoleName(string roleName)
        {
            List<User> users = new List<User>();
            Role role = UserHelper.GetRoleByName(roleName);
            if (role != null)
            {
                List<Actor> actors = UserHelper.GetRoleMembers(role);
                foreach (Actor actor in actors)
                {
                    if (actor.ActorType == ActorTypes.User)
                    {
                        users.Add(actor as User);
                    }
                }
            }

            return users;
        }

        /// <summary>
        /// 根据角色获取用户
        /// </summary>
        /// <param name="currentDept">部门</param>
        /// <param name="role">角色</param>
        /// <param name="needTrace">是否需要追溯。如果否，则只查当前部门；如果是，则逐级往上追溯</param>
        /// <returns></returns>
        public static List<User> GetUsersByRole(Department currentDept, Role role, bool needTrace)
        {
            if (currentDept == null)
            {
                throw new ArgumentNullException("currentDept", "查询上级部门角色用户时，传的的起始部门不能为空");
            }
            if (role == null)
            {
                throw new ArgumentNullException("role", "查询上级部门角色用户时，传的的目标角色不能为空");
            }

            string deptPath = currentDept.IDPath.ToUpper();
            string[] deptIDs = deptPath.Split(">".ToCharArray());
            Array.Reverse(deptIDs);
            List<Actor> actors = GetRoleMembers(role);

            List<User> users = new List<User>();
            if (actors.Count > 0)
            {
                // 需要追溯
                if (needTrace)
                {

                    foreach (string deptID in deptIDs)
                    {
                        users = (from a in actors
                                 where a.ActorType == ActorTypes.User
                                    && a.MemberScope.ToUpper().Contains(deptID.ToUpper())
                                 select (User)a).ToList<User>();
                        if (users.Count > 0)
                        {
                            break;
                        }
                    }
                    if (users.Count == 0)
                    {
                        users = (from a in actors
                                 where a.ActorType == ActorTypes.User
                                    && deptPath.IndexOf(((User)a).Dept.IDPath.ToUpper()) >= 0
                                 select (User)a).ToList<User>();
                    }
                }
                else
                {

                    users = (from a in actors
                             where a.ActorType == ActorTypes.User
                                && a.MemberScope.ToUpper().Contains(currentDept.ID.ToString().ToUpper())
                             select (User)a).ToList<User>();
                    if (users.Count == 0)
                    {
                        users = (from a in actors
                                 where a.ActorType == ActorTypes.User
                                    && ((User)a).Dept.ID == currentDept.ID
                                 select (User)a).ToList<User>();
                    }
                }

            }

            return users;
        }
        public static bool IsUserInRole(Actor actor, Role role)
        {
            List<Actor> members = UserHelper.GetRoleMembers(role);
            int count = (from m in members
                         where m.ID == actor.ID
                         select m).ToList<Actor>().Count;

            return count > 0;
        }

        /// <summary>
        /// 多个角色名用分号;隔开。比如“物资管理员;系统管理员”
        /// </summary>
        /// <param name="actor"></param>
        /// <param name="roleName"></param>
        /// <returns></returns>
        public static bool IsUserInRole(Actor actor, string roleName)
        {
            bool isInRole = false;
            if (actor.ActorType.ToString().ToLower() == roleName.ToLower())
            {
                isInRole = true;
            }
            else
            {
                List<Role> actorRoles = UserHelper.GetActorRole(actor.ID);
                string[] roleNameArray = roleName.Split(new char[] { ';' });
                for (int i = 0; i < roleNameArray.Length; i++)
                {
                    List<Role> inRoles = (from r in actorRoles
                                          where r.Name == roleNameArray[i]
                                          select r).ToList();
                    if (inRoles.Count > 0)
                    {
                        isInRole = true;
                        break;
                    }
                }
            }

            return isInRole;
        }

        /// <summary>
        /// 获取成员管理范围
        /// </summary>
        /// <param name="memberID">成员ID</param>
        /// <param name="roleName">角色名</param>
        /// <returns></returns>
        public static string GetMemberScope(string memberID,string roleName)
        {
            string sql = string.Format(@"select top 1 t1.Scope from ORG_RoleMembers t1
                                         inner join ORG_Roles t2 on t1.RoleID = t2.RoleID
                                         where t1.MemberID='{0}' and t2.RoleName='{1}'", memberID, roleName);

            object res = DBHelper.Instance.ExecuteScalar(CommandType.Text, sql);
            if (res != null)
                return res.ToString();
            return "";
        }



        public static List<Role> GetActorRole(string actorID)
        {
            List<Role> roles = new List<Role>();
            if (actorID != "")
            {
                string sql = string.Format(@"SELECT [RoleID], scope, deptname scopeDeptName
                                             FROM [dbo].[ORG_RoleMembers]
                                             left join org_deptments on scope = deptid
                                             WHERE [MemberID] = '{0}'", actorID.ToString());

                Database db = DBHelper.Instance;
                DbCommand cmd = db.GetSqlStringCommand(sql);

                DataSet ds = db.ExecuteDataSet(cmd);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        Role role = UserHelper.GetRoleByID(dr["RoleID"].ToString());
                        if (!dr.IsNull("scope"))
                        {
                            role.MemberScope = Convert.ToString(dr["scope"]);
                            role.MemberScopeName = Convert.ToString(dr["scopeDeptName"]);
                        }
                        else {
                            role.MemberScopeName = "全局角色";
                        }
                        roles.Add(role);
                    }
                }
            }

            return roles;
        }

        public static void SetActorRole(string actorID, string roleid, string scope)
        {
            if (actorID == "")
            {
                return;
            }

            string sql;
            sql = string.Format(@"
                if exists(select * from ORG_RoleMembers where RoleID='{0}' and MemberID='{1}')
                update ORG_RoleMembers set RoleID='{0}', scope='{2}' where RoleID='{0}' and MemberID='{1}'
                else
                insert into ORG_RoleMembers(RoleID,MemberID,MemberType, Scope)
                values('{0}','{1}', 0, '{2}')
                ", roleid, actorID.ToString(), scope);
            DBHelper.Instance.ExecuteNonQuery(CommandType.Text, sql);
        }

        public static void SetActorRole(string actorID, ActorTypes actorType, string[] roleIDs)
        {
            if (actorID == "")
            {
                return;
            }

            string sql;
            foreach (string roleid in roleIDs)
            {
                sql = string.Format(@"
if exists(select * from ORG_RoleMembers where RoleID='{0}' and MemberID='{1}')
update ORG_RoleMembers set RoleID='{0}' where RoleID='{0}' and MemberID='{1}'
else
insert into ORG_RoleMembers(RoleID,MemberID,MemberType)
values('{0}','{1}',{2})
", roleid, actorID.ToString(), (int)actorType);
                DBHelper.Instance.ExecuteNonQuery(CommandType.Text, sql);
            }

//            string roleIDStr = string.Join("','", roleIDs);
//            string sql_DeleteRole = string.Format(@"DELETE FROM [dbo].[ORG_RoleMembers]
//                                                    WHERE [MemberID] = '{0}'", actorID.ToString());
//            string sql_AddRole = string.Format(@"INSERT INTO [dbo].[ORG_RoleMembers]
//                                                       ([RoleID]
//                                                       ,[MemberID]
//                                                       ,[MemberType])
//                                                 SELECT [RoleID]
//                                                      ,'{0}'
//                                                      ,'{1}'
//                                                  FROM [dbo].[ORG_Roles]
//                                                  WHERE [RoleID] IN('{2}')", actorID.ToString(), (int)actorType, roleIDStr);
//            Database db = DBHelper.Instance;
//            DbConnection connection = db.CreateConnection();
//            DbTransaction tran = null;

//            DbCommand del_cmd = db.GetSqlStringCommand(sql_DeleteRole);
//            DbCommand add_cmd = db.GetSqlStringCommand(sql_AddRole);

//            try
//            {
//                AuthenticationUser cUser = SystemContext.Current.LoginAuthUser;
//                User targetUser = UserHelper.GetUserByID(actorID);
//                List<Role> OldRoleList = UserHelper.GetActorRole(actorID);
//                string OldRoleSetting = string.Join("，", (from r in OldRoleList
//                                                          select r.Name).ToList<string>().ToArray());


//                connection.Open();
//                tran = connection.BeginTransaction();

//                db.ExecuteNonQuery(del_cmd, tran);
//                db.ExecuteNonQuery(add_cmd, tran);

//                tran.Commit();


//                List<Role> NewRoleList = UserHelper.GetActorRole(actorID);
//                string NewRoleSetting = string.Join("，", (from r in NewRoleList
//                                                          select r.Name).ToList<string>().ToArray());

//                string msg = string.Format("用户“{0}({1})”于“{2}”修改了用户“{3}({4})”的角色。<br />原角色设置：{5}；<br />新角色设置：{6}"
//                                            , cUser.Name
//                                            , cUser.LoginID
//                                            , DateTime.Now.ToString()
//                                            , targetUser.Name
//                                            , targetUser.LoginID
//                                            , OldRoleSetting
//                                            , NewRoleSetting);
//                LogHelper.LogAction("修改角色设置", msg);
//            }
//            catch (Exception ex)
//            {
//                LogHelper.LogException(ex);
//                tran.Rollback();
//            }
//            finally
//            {
//                if (connection.State != ConnectionState.Closed)
//                {
//                    connection.Close();
//                }
//            }
        }

        private const string SQL_GET_SCOPEIDS = @"select top 1 scope 
                                                    from dbo.ORG_RoleMembers orm 
                                                        join dbo.ORG_Roles ors 
                                                        on orm.RoleID = ors.RoleID
                                                    where ors.RoleName='{0}'
                                                        and orm.MemberID='{1}'";

        public static string GetScopeIDs(string roleName,string userID) 
        {
            Database db = DBHelper.Instance;
            string sql = string.Format(SQL_GET_SCOPEIDS, roleName, userID);
            DbCommand cmd = db.GetSqlStringCommand(sql);
            Object obj = db.ExecuteScalar(cmd);
            if(obj == DBNull.Value)
                return null;
            else
                return (string)obj;
        }

        /// <summary>
        /// 获取扩展用户列表
        /// </summary>
        /// <param name="limite">返回数量限制</param>
        /// <param name="page">页码</param>
        /// <param name="total">总数</param>
        /// <param name="condition">查询字符串</param>
        /// <param name="dbParams">条件参数</param>
        /// <returns></returns>
        public static List<User> GetExtendUsers(int limite, int page, out int total, string condition, List<DbParameter> dbParams)
        {
            total = 0;
            Database db = DBHelper.Instance;
            string topClause = string.Empty;
            string whereClause = string.Format("WHERE UserType='3' {0}", condition);
            string countSql = string.Format("{0} {1}", SQL_ORG_User_Count, whereClause);
            if (limite > 0)
            {
                topClause = string.Format("Top {0}", limite.ToString());

                if (page > 1)
                {
                    int pageCount = (page - 1) * limite;
                    string subClause = string.Format(@"SELECT TOP {0} [UserID]
                                                       FROM [dbo].[ORG_Users] {1} {2}", pageCount.ToString(), whereClause, SQL_ORG_User_OrderBy);
                    whereClause += string.Format("AND [UserID] NOT IN({0})", subClause);
                }
            }
            string mainClause = string.Format(SQL_ORG_User, topClause);
            string sql = string.Format("{0} {1} {2}", mainClause, whereClause, SQL_ORG_User_OrderBy);
            DbCommand command = db.GetSqlStringCommand(sql);
            if (dbParams != null && dbParams.Count > 0)
            {
                command.Parameters.AddRange(dbParams.ToArray());
            }

            DataSet ds = db.ExecuteDataSet(command);
            List<User> users = new List<User>();
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                total = GetRecordCount(countSql, dbParams);
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    User user = new User(dr["UserID"].ToString());
                    FillUserInfo(dr, user);

                    users.Add(user);
                }
            }

            return users;
        }

        public static void DeleteUserRole(Guid actorID, Guid roleID)
        {
            string sql = String.Format("delete from org_rolemembers where memberId = '{0}' and roleId = '{1}'", actorID, roleID);
            DBHelper.Instance.ExecuteNonQuery(CommandType.Text, sql);
        }

        /// <summary>
        /// 判断用户是否为物资管理员室经理委托并且有工单需要批量审批的用户
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public static bool IsWzsDelegateUser(Guid userid)
        {
            string sql = String.Format(@"SELECT count(1) as recordNum
                                              FROM   dbo.WF_WorkItems wi
                                                     JOIN ORG_Users ou
                                                          ON  wi.OriginalPartID = ou.UserID
                                                     JOIN ORG_RoleMembers orm
                                                          ON  ou.UserID = orm.MemberID
                                                     JOIN ORG_Roles or1
                                                          ON  orm.RoleID = or1.RoleID
                                              WHERE  PartId = '{0}' 
                                                    and or1.rolename='物资管理员室经理' 
                                                    AND wi.FinishTime IS NULL", userid.ToString());
            Database db = DBHelper.Instance;
            DbCommand cmd = db.GetSqlStringCommand(sql);
            Object obj = db.ExecuteScalar(cmd);
            if(obj == DBNull.Value)
                return false;
            return (int)obj > 0 ? true : false;
        }

        /// <summary>
        /// 是否广州本部用户
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public static bool IsGuangZhouBenBuUser(string userId)
        {
            string sql = String.Format(@"select count(1) from org_users where dbo.f_GetTypeDeptID(deptid)='0D6C376F-0617-4161-81BE-C8088B12A46A' and userID='{0}'", userId);
            Database db = DBHelper.Instance;
            DbCommand cmd = db.GetSqlStringCommand(sql);
            Object obj = db.ExecuteScalar(cmd);
            if (obj == DBNull.Value)
                return false;
            return (int)obj > 0 ? true : false;
        }

        /// <summary>
        /// 获取根部门，包含郊市
        /// </summary>
        /// <returns></returns>
        public static DataSet GetLevelZeroDeptList()
        {
            string sql = @"select * from (
                                SELECT [DeptID], [DeptName], [orderno] FROM [ORG_Deptments] WHERE ([DeptLevel] = 3)
                                union all 
                                select '-1' as deptid,'全部' as deptname ,6 as [orderno]
                                ) t                                                        
                        order by orderno";
            Database db = DBHelper.Instance;
            DbCommand cmd = db.GetSqlStringCommand(sql);
            return db.ExecuteDataSet(cmd);
        }

        /// <summary>
        /// 根据用户ID获取根部门ID
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public static string GetRootDeptId(string userid)
        {
            string sql = string.Format(@"select top 1 dbo.f_GetTypeDeptID(deptid) from  org_users where userID='{0}'",userid.ToString());
            Database db = DBHelper.Instance;
            DbCommand cmd = db.GetSqlStringCommand(sql);
            Object obj = db.ExecuteScalar(cmd);
            if (obj == DBNull.Value)
                return "";
            return (string)obj;
        }


        public static string GetTopDeptID()
        {
            string sql = "select top 1 dbo.f_GetTopDeptID(@DeptID)";

            Database db = DBHelper.Instance;
            DbCommand command = db.GetSqlStringCommand(sql);

            DbParameter deptID = command.CreateParameter();
            deptID.ParameterName = "DeptID";
            deptID.Value = SystemContext.Current.LoginUser.Dept.ID;
            command.Parameters.Add(deptID);

            string TopDeptID = Convert.ToString(db.ExecuteScalar(command));

            return TopDeptID;
        }

        public static string GetTopDeptNameByUserID(string userId)
        {
            string sql = @"SELECT od.DeptName FROM ORG_Deptments od WHERE od.DeptID IN 
                                (SELECT top 1 dbo.f_GetTopDeptID(DeptID) FROM ORG_Users WHERE UserID=@UserID)";

            Database db = DBHelper.Instance;
            DbCommand command = db.GetSqlStringCommand(sql);

            DbParameter UserID = command.CreateParameter();
            UserID.ParameterName = "UserID";
            UserID.Value = userId;
            command.Parameters.Add(UserID);

            string topDeptName = Convert.ToString(db.ExecuteScalar(command));

            return topDeptName;
        }
    }
}
